﻿using Model;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Utility.Helper;

namespace BLL
{
    public class Yx_CustomerBLL : EntityBLL<Yx_Customer>
    {
        public override bool Update(Yx_Customer entity)
        {
            var sql = @"UPDATE YX_CUSTOMER
                        SET
	                        C_NAME = :C_NAME,
	                        C_PHONE = :C_PHONE,
	                        C_ADDRESS = :C_ADDRESS,
	                        N_COMPANY_ID = :N_COMPANY_ID,
                            N_STATE = :N_STATE,
                            C_REMARK= :C_REMARK,
                            C_REAL_NAME = :C_REAL_NAME,
                            N_CUSTOMER_TYPE = :N_CUSTOMER_TYPE
                        WHERE
	                        N_ID = :N_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":N_ID", entity.N_ID, "int");
            parms.Add(":C_NAME", entity.C_NAME, "string");
            parms.Add(":C_PHONE", entity.C_PHONE, "string");
            parms.Add(":C_ADDRESS", entity.C_ADDRESS, "string");
            parms.Add(":N_COMPANY_ID", entity.N_COMPANY_ID, "int");
            parms.Add(":N_STATE", entity.N_STATE, "int");
            parms.Add(":C_REMARK", entity.C_REMARK, "string");
            parms.Add(":C_REAL_NAME", entity.C_REAL_NAME, "string");
            parms.Add(":N_CUSTOMER_TYPE", entity.N_CUSTOMER_TYPE, "int");
            //sql = string.Format(sql, entity.N_ID, entity.C_NAME, entity.C_PHONE, entity.C_ADDRESS, entity.N_COMPANY_ID,entity.N_STATE,entity.C_REMARK,entity.C_REAL_NAME);
            return NoQuery(sql, parms) > 0 ? true : false;
        }

        public override bool Insert(Model.Yx_Customer entity)
        {
            entity.N_ID = GetSequence("SEQ_N_CUSTOMER_ID");
            var sql = @"INSERT INTO YX_CUSTOMER(
                            N_ID,
                            C_NAME,
                            C_PHONE,
                            C_ADDRESS,
                            N_COMPANY_ID,
                            N_STATE,
                            C_REMARK,
                            C_REAL_NAME,
                            N_CUSTOMER_TYPE
                        ) VALUES(
                            :N_ID,
                            :C_NAME,
                            :C_PHONE,
                            :C_ADDRESS,
                            :N_COMPANY_ID,
                            :N_STATE,
                            :C_REMARK,
                            :C_REAL_NAME,
                            :N_CUSTOMER_TYPE
                        )";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":N_ID", entity.N_ID, "int");
            parms.Add(":C_NAME", entity.C_NAME, "string");
            parms.Add(":C_PHONE", entity.C_PHONE, "string");
            parms.Add(":C_ADDRESS", entity.C_ADDRESS, "string");
            parms.Add(":N_COMPANY_ID", entity.N_COMPANY_ID, "int");
            parms.Add(":N_STATE", entity.N_STATE, "int");
            parms.Add(":C_REMARK", entity.C_REMARK, "string");
            parms.Add(":C_REAL_NAME", entity.C_REAL_NAME, "string");
            parms.Add(":N_CUSTOMER_TYPE", entity.N_CUSTOMER_TYPE, "int");
            return NoQuery(sql, parms) > 0 ? true : false;
        }

        public override Yx_Customer Find(object key)
        {
            var entitys = base.Where("N_ID = " + key.ToString());
            if (entitys.Count == 1)
            {
                return entitys[0];
            }
            return null;
        }

        #region 根据id查询客户公司

        public List<Yx_Customer> GetEntitys(string whereStr, string orderbyStr, int skip, int take, out int count)
        {
            var dt = GetData(whereStr, orderbyStr, skip, take, out count);
            var entitys = new List<Yx_Customer>();
            foreach (DataRow item in dt.Rows)
            {
                entitys.Add(ConvertToEntity(item));
            }
            return entitys;
        }

        public DataTable GetData(string whereStr, string orderbyStr, int skip, int take, out int count)
        {
            var sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine(@"SELECT *
  FROM YX_CUSTOMER T1
 WHERE T1.n_Company_Id in
       (SELECT CMY_ID
          FROM SYS_COMPANY
         START WITH CMY_ID = " + whereStr + @"
        CONNECT BY PRIOR CMY_ID = CMY_PARENTID)");

            if (orderbyStr != null)
            {
                sqlBuilder.AppendLine(" ORDER BY " + orderbyStr);
            }
            count = SqlCount(sqlBuilder.ToString());
            return PageQuery(sqlBuilder.ToString(), skip, take);
        }

        public DataTable GetData2(string whereStr)
        {
            string sql = @"SELECT * FROM YX_CUSTOMER where 1=1 " + whereStr;
            return Query(sql); ;
        }

        private Yx_Customer ConvertToEntity(DataRow row)
        {
            if (row == null)
            {
                return null;
            }
            var entity = new Yx_Customer
            {
                N_ID = Convert.ToDecimal(row["N_ID"]),
                C_NAME = Convert.ToString(row["C_NAME"]),
                C_PHONE = row.GetValue<string>("C_PHONE"),
                C_ADDRESS = row.GetValue<string>("C_PHONE"),
                N_COMPANY_ID = row.GetValue<decimal>("N_COMPANY_ID"),
                C_REMARK = row.GetValue<string>("C_REMARK"),
                N_STATE = row.GetValue<decimal>("N_STATE"),
                C_REAL_NAME = row.GetValue<string>("C_REAL_NAME"),
                N_CUSTOMER_TYPE = row.GetValue<int?>("N_CUSTOMER_TYPE")
            };
            return entity;
        }

        /// <summary>
        /// 写入客户公司信息
        /// </summary>
        /// <param name="arry"></param>
        /// <param name="user"></param>
        /// <returns></returns>
        public int ReadTeamplete(JArray arry, Sys_User user)
        {
            //BLL
            var companyBll = new Sys_CompanyBLL();
            //装载模型
            var entitys = new List<Yx_Customer>();
            foreach (var item in arry)
            {
                string name, phone, address, companyName, remark, stateMsg, realName, customerName;
                int? companyId;
                name = item.Value<string>("C_NAME");
                phone = item.Value<string>("C_PHONE");
                address = item.Value<string>("C_ADDRESS");
                companyName = item.Value<string>("COMPANY_NAME");
                companyId = item.Value<int?>("COMPANY_ID");
                remark = item.Value<string>("REMARK");
                stateMsg = item.Value<string>("STATE_MSG");
                realName = item.Value<string>("REAL_NAME");
                customerName = item.Value<string>("CUSTOMER_NAME");
                var entity = new Yx_Customer();
                entitys.Add(entity);
                entity.C_NAME = name.Trim();
                entity.C_PHONE = phone.Trim();
                entity.C_ADDRESS = address.Trim();
                try
                {
                    entity.N_COMPANY_ID = companyId == null ? (decimal)companyId : companyBll.GetInfoByName(companyName).CMY_ID;
                }
                catch (Exception)
                {
                    throw new Exception(companyName + "【公司】不存在。请检测信息");
                }
                entity.C_REAL_NAME = realName.Trim();
                entity.C_REMARK = remark.Trim();
                entity.N_CUSTOMER_TYPE = CustomerNameConvertById(name);
                entity.N_STATE = 1;
            }
            var sql = @"SELECT
                            *
                        FROM YX_CUSTOMER T1
                        WHERE
                            T1.C_NAME = :NAME AND
                            T1.N_COMPANY_ID = :COMAPNY_ID";
            var count = 0;

            #region 事物

            this.BeginTran();
            try
            {
                foreach (var entity in entitys)
                {
                    var parms = new List<Tuple<string, string, string>>();
                    parms.Add(":NAME", entity.C_NAME);
                    parms.Add(":COMAPNY_ID", entity.N_COMPANY_ID);
                    var _entity = ConvertEntity(Query(sql, parms));
                    if (_entity == null)
                    {
                        Insert(entity);
                    }
                    else
                    {
                        Update(entity);
                    }
                    count++;
                }
                this.Commit();
                return count;
            }
            catch (Exception)
            {
                this.Commit(true);
                throw;
            }

            #endregion 事物
        }

        /// <summary>
        /// 用户类型转换String转Id
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public int? CustomerNameConvertById(string name)
        {
            switch (name)
            {
                case "工业用户":
                    return 0;

                case "居民,商业,集体用户":
                    return 1;

                case "压缩天然气（CNG）":
                    return 2;

                case "液化天然气（LNG)":
                    return 3;

                default:
                    return null;
            }
        }

        #region MyRegion

        public DataTable GetInfoByPage(string whereStr, string orderByStr, int? skip, int? take)
        {
            var sql = @"SELECT
                            T1.N_ID,
                            T1.C_NAME,
                            T1.C_PHONE,
                            T1.C_ADDRESS,
                            T1.N_COMPANY_ID,
                            T2.CMY_NAME AS C_COMPANY_NAME,
                            T1.C_REMARK,
                            T1.N_STATE,
                            T1.C_REAL_NAME,
                            T1.N_CUSTOMER_TYPE,
                            CASE T1.N_STATE
                                WHEN 0 THEN '停用'
                                WHEN 1 THEN '使用'
                                ELSE '停用'
                            END AS C_STATE_MSG,
                            CASE T1.N_CUSTOMER_TYPE
                                WHEN 0 THEN '工业用户'
                                WHEN 1 THEN '居民,商业,集体用户'
                                WHEN 2 THEN '压缩天然气（CNG）'
                                WHEN 3 THEN '液化天然气（LNG)'
                                ELSE '其它'
                            END AS C_CUSTOMER_TYPE_MSG
                        FROM YX_CUSTOMER T1
                        LEFT JOIN SYS_COMPANY T2 ON T1.N_COMPANY_ID = T2.CMY_ID";
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += " WHERE " + whereStr;
            }
            if (!string.IsNullOrEmpty(orderByStr))
            {
                sql += " ORDER BY " + orderByStr;
            }
            var dt = PageQuery(sql, skip, take);
            return dt;
        }

        public int GetInfoByPageCount(string whereStr, string orderByStr)
        {
            var sql = @"SELECT
                            COUNT(*)
                        FROM YX_CUSTOMER T1
                        LEFT JOIN SYS_COMPANY T2 ON T1.N_COMPANY_ID = T2.CMY_ID";
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += " WHERE " + whereStr;
            }
            if (!string.IsNullOrEmpty(orderByStr))
            {
                sql += " ORDER BY " + orderByStr;
            }
            return SqlCount(sql);
        }

        #endregion MyRegion

        #endregion 根据id查询客户公司
    }
}